package de.hsmannheim.poolraumapp.db;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.joda.time.DateTimeConstants;
import org.joda.time.LocalTime;
import org.joda.time.format.DateTimeFormat;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;
import de.hsmannheim.poolraumapp.model.LessonBlock;
import de.hsmannheim.poolraumapp.model.LessonBlocks;
import de.hsmannheim.poolraumapp.model.Result;
import de.hsmannheim.poolraumapp.model.Room;
import de.hsmannheim.poolraumapp.model.RoomQuery;
import de.hsmannheim.poolraumapp.model.RoomQuery.What;
import de.hsmannheim.poolraumapp.model.RoomQuery.WhatOS;
import de.hsmannheim.poolraumapp.model.RoomReservation;
import de.hsmannheim.poolraumapp.model.RoomReservations;
import de.hsmannheim.poolraumapp.model.Rooms;

public class RoomSearcher {
	private Context context;
	private DBHelper dbHelper;
	
 	public RoomSearcher(Context context) {
		this.context = context;
		this.dbHelper = new DBHelper(context);
	}
 	
 	private static final String TAG = RoomSearcher.class.getName();
 	
 	private static final String DATE_TIME_PATTERN = "YYYY-MM-dd HH:mm:ss";
 	private static final String TIME_PATTERN = "HH:mm:ss";
 	
	private static final String TABLE_RESERVATION = "reservation";
	private static final String TABLE_ROOM = "room";
	private static final String TABLE_LESSON_BLOCK = "lesson_block";
	private static final String TABLE_RESULT = "result";
	private static final String TABLE_LAST_MODIFIED = "last_modified";
	
	private static final String COLUMN_ID = BaseColumns._ID;
	
	private static final String COLUMN_BLOCK = "block";
	private static final String COLUMN_DAY = "day";
	private static final String COLUMN_BUILDING = "building";
	private static final String COLUMN_ROOM_NUMBER = "room_number";
	private static final String COLUMN_DEPARTMENT = "department";
	
	private static final String COLUMN_HAS_MAC = "has_mac";
	private static final String COLUMN_HAS_WINDOWS = "has_windows";
	private static final String COLUMN_HAS_LINUX = "has_linux";
			
	private static final String COLUMN_BEGIN = "begin";
	private static final String COLUMN_END = "end";
	
	private static final String COLUMN_TABLE_NAME = "table_name";
	private static final String COLUMN_MODIFICATION_DATE = "modification_date";
	
	private static final String DAY_BEGIN = "00:00:00";
	private static final String DAY_END = "23:59:59";
	
	private static final String TMP_TABLE_DAY = "tmp_day";
	private static final String TMP_TABLE_RESERVATION = "tmp_reservation";
	
	private class DBHelper extends SQLiteOpenHelper {

		private static final String DATABASE = "poolraumApp.db";
		private static final int VERSION = 7;
		
		public DBHelper(Context context) {
			super(context, DATABASE, null, VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			create(db, TABLE_RESERVATION, 
					COLUMN_ID, "int primary key",
					COLUMN_BUILDING, "char(1)",
					COLUMN_ROOM_NUMBER, "varchar(4)",
					COLUMN_DAY, "int",				
					COLUMN_BLOCK, "int",
					COLUMN_DEPARTMENT, "char(1)");
			create(db, TABLE_ROOM, 
					COLUMN_ID, "int primary key",
					COLUMN_BUILDING, "char(1)",
					COLUMN_ROOM_NUMBER, "varchar(4)",
					COLUMN_HAS_MAC, "int",
					COLUMN_HAS_WINDOWS, "int",
					COLUMN_HAS_LINUX, "int");
			create(db, TABLE_LESSON_BLOCK, 
					COLUMN_ID, "int primary key",
					COLUMN_BLOCK, "int",
					COLUMN_DEPARTMENT, "char(1)",
					COLUMN_BEGIN, "char(8)",
					COLUMN_END, "char(8)");			
			create(db, TABLE_RESULT, 
					COLUMN_ID, "int primary key",
					COLUMN_BUILDING, "char(1)",
					COLUMN_ROOM_NUMBER, "varchar(4)",					
					COLUMN_DAY, "int",
					COLUMN_BEGIN, "char(8)",
					COLUMN_END, "char(8)",
					COLUMN_HAS_MAC, "int",
					COLUMN_HAS_WINDOWS, "int",
					COLUMN_HAS_LINUX, "int");
			create(db, TABLE_LAST_MODIFIED, 
					COLUMN_ID, "int primary key",
					COLUMN_TABLE_NAME, "varchar(64)",
					COLUMN_MODIFICATION_DATE, "char(19)");			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			drop(db, TABLE_RESERVATION);
			drop(db, TABLE_ROOM);
			drop(db, TABLE_LESSON_BLOCK);
			drop(db, TABLE_RESULT);
			drop(db, TABLE_LAST_MODIFIED);
			onCreate(db);
		}
		
		private void create(SQLiteDatabase db, String table, String... columns) {
			StringBuilder sql = new StringBuilder("create table " + table + " ( ");
			for (int i = 0; i < columns.length; i+=2) {
				sql.append(columns[i]);
				sql.append(' ');
				sql.append(columns[i+1]);
				if (i != columns.length - 2)
					sql.append(", ");
			}
			sql.append(");");
			db.execSQL(sql.toString());
		}
		
		private void drop(SQLiteDatabase db, String table) {
			try {
				db.execSQL("drop table " + table + ";");
			} catch (SQLException e) {
			}
		}
	}
	
	public void setReservations(RoomReservations reservations) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(TABLE_RESERVATION, null, null);			
			ContentValues values = new ContentValues();
			for (RoomReservation reservation : reservations) {
				values.clear();
				
				values.put(COLUMN_BLOCK, reservation.getBlock());
				values.put(COLUMN_DAY, reservation.getDay());
				values.put(COLUMN_BUILDING, reservation.getBuilding());
				values.put(COLUMN_ROOM_NUMBER, reservation.getRoomNumber());
				values.put(COLUMN_DEPARTMENT, reservation.getDepartment());
				db.insert(TABLE_RESERVATION, null, values);
			}
			setLastModified(db, TABLE_RESERVATION, reservations.getLastModified());			
			db.setTransactionSuccessful();
		}
		finally {
			db.endTransaction();
			db.close();
		}
	}

	private void setLastModified(SQLiteDatabase db, String table, DateTime lastModified) {
		ContentValues values = new ContentValues();
		db.delete(TABLE_LAST_MODIFIED, COLUMN_TABLE_NAME + "='" + table + "'", null);
		values.put(COLUMN_TABLE_NAME, table);
		values.put(COLUMN_MODIFICATION_DATE, lastModified.toString(DATE_TIME_PATTERN));
		db.insert(TABLE_LAST_MODIFIED, null, values);
	}

	private String and(String... s) {
		return " (" + StringUtils.join(s, " ) and ( ") + " ) ";
	}
	
	private String or(String... s) {
		return " (" + StringUtils.join(s, " ) or ( ") + " ) ";
	}
	
	public List<Result> search(RoomQuery roomQuery) {
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query(TABLE_RESULT, null, 
					and(
						whenDay(roomQuery),
						whenTime(roomQuery),
						where(roomQuery),
						what(roomQuery)
					),
			null, null, null, null);
			
			List<Result> results = new ArrayList<Result>(cursor.getCount());
			while (cursor.moveToNext()) {
				Result result = new Result();
				result.setBuilding(cursor.getString(cursor.getColumnIndex(COLUMN_BUILDING)));
				result.setRoomNumber(cursor.getString(cursor.getColumnIndex(COLUMN_ROOM_NUMBER)));
				result.setDay(cursor.getInt(cursor.getColumnIndex(COLUMN_DAY)));
				result.setBegin(toLocalTime(cursor.getString(cursor.getColumnIndex(COLUMN_BEGIN))));
				result.setEnd(toLocalTime(cursor.getString(cursor.getColumnIndex(COLUMN_END))));
				result.setHasWindows(cursor.getInt(cursor.getColumnIndex(COLUMN_HAS_WINDOWS)) == 1);
				result.setHasLinux(cursor.getInt(cursor.getColumnIndex(COLUMN_HAS_LINUX)) == 1);
				result.setHasMac(cursor.getInt(cursor.getColumnIndex(COLUMN_HAS_MAC)) == 1);
				results.add(result);
			}
			return results;
		}
		finally {
			if (db != null)
				db.close();
		}
	}
	
	private String whenDay(RoomQuery roomQuery) {
		switch (roomQuery.getDay()) {
		case TODAY:
			return equals(COLUMN_DAY, "" + new DateTime().getDayOfWeek());
		case WEEKDAY:
			return equals(COLUMN_DAY, "" + roomQuery.getWeekday());
		case DATE:
			return equals(COLUMN_DAY, "" + roomQuery.getDate().getDayOfWeek());
		}
		throw new RuntimeException();
	}

	private String whenTime(RoomQuery roomQuery) {
		switch (roomQuery.getTime()) {
		case NOW:
			String now = "'" + toString(new LocalTime()) + "'";
			return and(
					lessEquals(COLUMN_BEGIN, now),
					greaterEquals(COLUMN_END, now)
				);
		case POINT:
			String timePoint = "'" + toString(roomQuery.getTimePoint()) + "'";
			return and(
					lessEquals(COLUMN_BEGIN, timePoint),
					greaterEquals(COLUMN_END, timePoint)
				);
		case SPAN:
			String from = "'" + toString(roomQuery.getFrom()) + "'";
			String to = "'" + toString(roomQuery.getTo()) + "'";
			return or(
					and(
						lessEquals(COLUMN_BEGIN, from),
						greaterEquals(COLUMN_END, from)
					),
					and(
						lessEquals(COLUMN_BEGIN, to),
						greaterEquals(COLUMN_END, to)
					),
					and(
						greaterEquals(COLUMN_BEGIN, from),
						lessEquals(COLUMN_END, to)
					)
			);
		}
		return alwaysTrue();
	}

	private String where(RoomQuery roomQuery) {
		switch (roomQuery.getWhere()) {
		case ANYWHERE:
			return alwaysTrue();
		case BUILDINGS:
			if (roomQuery.getBuildings().isEmpty()) return alwaysFalse();
			List<String> conditions = new ArrayList<String>(); 
			for (String building : roomQuery.getBuildings()) {
				conditions.add(equals(COLUMN_BUILDING, "'" + building + "'"));
			}
			return StringUtils.join(conditions, " or ");
		}
		throw new RuntimeException();
	}
	
	private String alwaysTrue() {
		return "1=1";
	}
	private String alwaysFalse() {
		return "1=0";
	}
	
	private String what(RoomQuery roomQuery) {
		if (roomQuery.getOS().isEmpty()) return alwaysFalse();
		List<String> conditions = new ArrayList<String>(); 
		for (WhatOS os : roomQuery.getOS()) {
			conditions.add(" has_" + os.name().toLowerCase(Locale.ENGLISH) + "=1 ");
		}
		return StringUtils.join(conditions, (roomQuery.getWhat() == What.ALL_OF) ? " and " : " or ");
	}

	public DateTime getRoomsLastModified() {		
		return getLastModified(TABLE_ROOM);		
	}
	
	public DateTime getReservationsLastModified() {		
		return getLastModified(TABLE_RESERVATION);		
	}
	
	public DateTime getLessonBlocksLastModified() {		
		return getLastModified(TABLE_LESSON_BLOCK);		
	}

	private DateTime getLastModified(String table) {
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getReadableDatabase();
			Cursor cursor = db.query(TABLE_LAST_MODIFIED, new String[] { COLUMN_MODIFICATION_DATE}, 
					COLUMN_TABLE_NAME + "='" + table + "'", null, null, null, null);
			
			if (cursor.moveToNext()) {
				return toDateTime(cursor.getString(cursor.getColumnIndex(COLUMN_MODIFICATION_DATE)));
			}
			return null;
		}
		finally {
			if (db != null)
				db.close();
		}
	}

	private DateTime toDateTime(String string) {
		return DateTimeFormat.forPattern(DATE_TIME_PATTERN).parseDateTime(string);
	}
	
	private LocalTime toLocalTime(String string) {
		return DateTimeFormat.forPattern(TIME_PATTERN).parseLocalTime(string);
	}

	public void setRooms(Rooms rooms) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(TABLE_ROOM, null, null);			
			ContentValues values = new ContentValues();
			for (Room room : rooms) {
				values.clear();
				
				values.put(COLUMN_BUILDING, room.getBuilding());
				values.put(COLUMN_ROOM_NUMBER, room.getNumber());
				values.put(COLUMN_HAS_MAC, room.hasMac());
				values.put(COLUMN_HAS_WINDOWS, room.hasWindows());
				values.put(COLUMN_HAS_LINUX, room.hasLinux());
				db.insert(TABLE_ROOM, null, values);
			}
			setLastModified(db, TABLE_ROOM, rooms.getLastModified());			
			db.setTransactionSuccessful();
		}
		finally {
			db.endTransaction();
			db.close();
		}
		
	}

	public void setLessonBlocks(LessonBlocks lessonBlocks) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(TABLE_LESSON_BLOCK, null, null);			
			ContentValues values = new ContentValues();
			for (LessonBlock lessonBlock : lessonBlocks) {
				values.clear();
				
				values.put(COLUMN_BLOCK, lessonBlock.getBlock());
				values.put(COLUMN_DEPARTMENT, lessonBlock.getDepartment());
				values.put(COLUMN_BEGIN, toString(lessonBlock.getBegin()));
				values.put(COLUMN_END, toString(lessonBlock.getEnd()));
				db.insert(TABLE_LESSON_BLOCK, null, values);
			}
			setLastModified(db, TABLE_LESSON_BLOCK, lessonBlocks.getLastModified());			
			db.setTransactionSuccessful();
		}
		finally {
			db.endTransaction();
			db.close();
		}
	}
	
	private String toString(LocalTime localTime) {
		return localTime.toString(TIME_PATTERN);
	}
	
	private String toString(DateTime dateTime) {
		return dateTime.toString(DATE_TIME_PATTERN);
	}

	private String col(String table, String col) {
		return table + "." + col;
	}
	
	private String leftJoin(String table, String... conditions) {
		return " left join " + table + ((conditions.length > 0 ) ? " on " + StringUtils.join(conditions, " and ") : " ");
	}
	
	private String fullJoin(String table, String... conditions) {
		return " join " + table + ((conditions.length > 0 ) ? " on " + StringUtils.join(conditions, " and ") : " ");
	}
	
	private String equals(String left, String right) {
		return left + "=" + right;
	}
	private String greaterEquals(String left, String right) {
		return left + ">=" + right;
	}
	private String lessEquals(String left, String right) {
		return left + "<=" + right;
	}
	
	private String order(String... cols) {
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < cols.length; i++) {
			builder.append(cols[i] + " asc ");
			if (i < cols.length - 1) {
				builder.append(", ");
			}
		}
		return builder.toString();
	}	
	
	public void updateResults() {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(TABLE_RESULT, null, null);
			SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
			queryBuilder.setTables(TABLE_ROOM +
				leftJoin(days()) +
				leftJoin(
					select(TABLE_RESERVATION, TMP_TABLE_RESERVATION, 
						fullJoin(TABLE_LESSON_BLOCK,
							equals(	col(TABLE_RESERVATION, COLUMN_BLOCK),
									col(TABLE_LESSON_BLOCK, COLUMN_BLOCK)),
							equals(	col(TABLE_RESERVATION, COLUMN_DEPARTMENT),
									col(TABLE_LESSON_BLOCK, COLUMN_DEPARTMENT))					
						)
					),
					equals(	col(TMP_TABLE_RESERVATION, COLUMN_BUILDING),
							col(TABLE_ROOM, COLUMN_BUILDING)),
					equals(	col(TMP_TABLE_RESERVATION, COLUMN_ROOM_NUMBER),
							col(TABLE_ROOM, COLUMN_ROOM_NUMBER)),
					equals(	col(TMP_TABLE_RESERVATION, COLUMN_DAY),
							col(TMP_TABLE_DAY, COLUMN_DAY))										
				));
			Cursor cursor = queryBuilder.query(db, new String[] {
					col(TABLE_ROOM, COLUMN_BUILDING),
					col(TABLE_ROOM, COLUMN_ROOM_NUMBER),
					col(TABLE_ROOM, COLUMN_HAS_MAC),
					col(TABLE_ROOM, COLUMN_HAS_WINDOWS),
					col(TABLE_ROOM, COLUMN_HAS_LINUX),
					col(TMP_TABLE_DAY, COLUMN_DAY),
					col(TMP_TABLE_RESERVATION, COLUMN_BLOCK),
					col(TMP_TABLE_RESERVATION, COLUMN_BEGIN),
					col(TMP_TABLE_RESERVATION, COLUMN_END),
				}, null, null, null, null, order(
					col(TABLE_ROOM, COLUMN_BUILDING),
					col(TABLE_ROOM, COLUMN_ROOM_NUMBER),
					col(TMP_TABLE_DAY, COLUMN_DAY),
					col(TMP_TABLE_RESERVATION, COLUMN_BLOCK)
			));
			ContentValues values = new ContentValues();
			String begin = DAY_BEGIN;
			
			int dayIndex = cursor.getColumnIndex(COLUMN_DAY);
			int beginIndex = cursor.getColumnIndex(COLUMN_BEGIN);
			int buildingIndex = cursor.getColumnIndex(COLUMN_BUILDING);
			int roomNumberIndex = cursor.getColumnIndex(COLUMN_ROOM_NUMBER);

			ContentValues out = new ContentValues();
			while (cursor.moveToNext()) { 

				out.clear();
				DatabaseUtils.cursorRowToContentValues(cursor, out);
				System.out.println(out);

				boolean foundLessonBlock = !cursor.isNull(beginIndex);
								
				values.clear();
				DatabaseUtils.cursorRowToContentValues(cursor, values);
				values.remove(COLUMN_BLOCK);
				if (!foundLessonBlock) {
					insertWholeDay(db, values, cursor.getInt(dayIndex));
				}
				else {
					String end = values.getAsString(COLUMN_END);
					values.put(COLUMN_END, values.getAsString(COLUMN_BEGIN));
					values.put(COLUMN_BEGIN, begin);
					db.insert(TABLE_RESULT, null, values);
					begin = end;
					String building = values.getAsString(COLUMN_BUILDING);
					String roomNumber = values.getAsString(COLUMN_ROOM_NUMBER);
					int day = values.getAsInteger(COLUMN_DAY);
					if (!cursor.moveToNext() || 
						!cursor.getString(buildingIndex).equals(building) ||
						!cursor.getString(roomNumberIndex).equals(roomNumber) ||
						cursor.getInt(dayIndex) != day) {
						
						values.put(COLUMN_BEGIN, begin);
						values.put(COLUMN_END, DAY_END);
						db.insert(TABLE_RESULT, null, values);
						begin = DAY_BEGIN;
					}
					cursor.moveToPrevious();
				}
			} 
			db.setTransactionSuccessful();
		}
		finally {
			db.endTransaction();
			db.close();
		}
	}

	private String select(String table, String tmpTable,
			String join) {
		return "(select * from " + table + " " + join + ") as " + tmpTable; 
	}

	private String days() {
		List<String> rows = new ArrayList<String>();
		for (int d = DateTimeConstants.MONDAY; d <= DateTimeConstants.SUNDAY; d++) {
				rows.add(" select " + d + " as " + COLUMN_DAY + " ");
		}
		return "(" + StringUtils.join(rows, " union ") + ") as " + TMP_TABLE_DAY + " ";
	}

	private void insertWholeDay(SQLiteDatabase db, ContentValues values, int day) {
		values.put(COLUMN_DAY, day);
		values.put(COLUMN_BEGIN, DAY_BEGIN);
		values.put(COLUMN_END, DAY_END);
		db.insert(TABLE_RESULT, null, values);
	}
}
